package com.javer.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.javer.dao.support.DataSourceDaoSupport;
import com.javer.domain.Company;
import com.javer.domain.PageView;
import com.mysql.jdbc.Statement;

@Repository
public class CompanyDao extends DataSourceDaoSupport {
	private static CompanyMapper companyMapper = new CompanyMapper();
	private static final String COUNT = "select count(*) from company";
	
	public void getCompanyByPage(PageView<Company> pv) {
		String sql = "select c.*,(select count(*) from comment where ctype=0 and tid=c.id) commentcount from company c order by id desc";
		getByPage(pv, "select count(*) from company", sql);
	}
	
	@Override
	public RowMapper getMapper() {
		return companyMapper;
	}
	
	public void updateCompany(Company company) {
		Assert.notNull(company, "company must not be null");
		String sql = "update company set cname=?,cscale=?,caddress=?,cbusiness=?,cdesc=?,cscore=? where id=?";
		Object[] args = new Object[]{company.getCname(), company.getCscale(), company.getCaddress(), 
				company.getCbusiness(), company.getCdesc(), company.getCscore(), company.getId()};
		jdbcTemplate.update(sql, args);
	}
	
	public PageView<Company> findCompany(PageView<Company> pv, String name) {
		Object[] args = new Object[]{"%" + name + "%"};
		String count = "select count(*) from company where cname like ?";
		return getByPage(pv, count, args, "select c.*, (select count(*) from comment where tid=c.id) commentcount from company c where cname like ?", args, companyMapper);
	}

	public PageView<Company> getHotCompany(PageView<Company> pv) {
		return getByPage(pv, COUNT, "select c.*, (select count(*) from comment where tid=c.id) commentcount from company c order by commentcount desc", companyMapper);
	}
	
	public PageView<Company> getGoodCompany(PageView<Company> pv) {
		return getByPage(pv, COUNT, "select c.*,(select count(*) from comment where tid=c.id) commentcount from company c order by cscore desc", companyMapper);
	}
	
	public PageView<Company> getBadCompany(PageView<Company> pv) {
		return getByPage(pv, COUNT, "select c.*,(select count(*) from comment where tid=c.id) commentcount from company c order by cscore asc", companyMapper);
	}
	
	public PageView<Company> getNewCompany(PageView<Company> pv) {
		return getByPage(pv, COUNT, "select c.*,(select count(*) from comment where tid=c.id) commentcount from company c order by id desc", companyMapper);
	}
	
	public List<Company> getHotCompany(int limit) {
		if (limit <= 0) {
			limit = 500;
		}
		return jdbcTemplate.query("select c.*, (select count(*) from comment where tid=c.id) commentcount from company c order by commentcount desc limit " + limit, companyMapper);
	}
	
	public List<Company> getGoodCompany(int limit) {
		if (limit <= 0) {
			limit = 500;
		}
		return jdbcTemplate.query("select c.*,(select count(*) from comment where tid=c.id) commentcount from company c order by cscore desc limit " + limit, companyMapper);
	}
	
	public List<Company> getBadCompany(int limit) {
		if (limit <= 0) {
			limit = 500;
		}
		return jdbcTemplate.query("select c.*,(select count(*) from comment where tid=c.id) commentcount from company c order by cscore asc limit " + limit, companyMapper);
	}
	
	public List<Company> getNewCompany(int limit) {
		if (limit <= 0) {
			limit = 500;
		}
		return jdbcTemplate.query("select c.*,(select count(*) from comment where tid=c.id) commentcount from company c order by id desc limit " + limit, companyMapper);
	}
	
	public void updateCompanyScore(Company company) {
		Assert.notNull(company, "company must not be null");
		jdbcTemplate.update("update company set cscore=cscore+("+ company.getCscore() +") where id="+company.getId());
	}
	
	public int addCompany(Company company) {
		Assert.notNull(company, "company must not be null");
		final Object[] args = new Object[]{company.getCname(), company.getCscale(), company.getCaddress(), company.getCdesc(), 
				company.getCimages(), company.getCmap(), company.getCbusiness()};
		final String sql = "insert into company(cname,cscale,caddress,cdesc,cimages,cmap,cbusiness) values(?,?,?,?,?,?,?)";
		PreparedStatementCreator psc = new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				for (int i = 0; i < args.length; i++) {
					ps.setObject(i + 1, args[i]);
				}
				return ps;
			}
		};
		KeyHolder kh = new GeneratedKeyHolder();
		jdbcTemplate.update(psc, kh);
		return kh.getKey().intValue();
	}
	
	public int getCompanyCount() {
		return jdbcTemplate.queryForInt("select count(*) from company");
	}
	
	public Company getCompanyById(int id) {
		return (Company) jdbcTemplate.queryForObject("select c.*, 0 commentcount from company c where id=" + id, companyMapper);
	}
	
	public List<Company> getAllCompany() {
		return jdbcTemplate.query("select * from company", companyMapper);
	}
	
	static class CompanyMapper implements RowMapper {

		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Company c = new Company();
			c.setId(rs.getInt("id"));
			c.setCname(rs.getString("cname"));
			c.setCdesc(rs.getString("cdesc"));
			c.setCscale(rs.getString("cscale"));
			c.setCbusiness(rs.getString("cbusiness"));
			c.setCscore(rs.getInt("cscore"));
			c.setCommentCount(rs.getInt("commentcount"));
			c.setCaddress(rs.getString("caddress"));
			return c;
		}
		
	}
}
